package utility;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import dao.ConnPool;
import dao.DAOException;

public class DayspentCal {
	public DayspentCal()
	{
		
	}
	
	public static int CalcDaysSpent(java.util.Date days, java.util.Date daye,int DurationDays)
	{

		 ArrayList<Calendar> FirstYearHoliday = new ArrayList<Calendar>();
		 ArrayList<Calendar> SecondYearHoliday = new ArrayList<Calendar>();	
		 
		Calendar dstart = Calendar.getInstance();
		Calendar dend   = Calendar.getInstance();
		dstart.setTime(days);
		dend.setTime(daye);
		if(dstart.get(Calendar.YEAR) == 2012 && dend.get(Calendar.YEAR) == 2013)
		{
			FirstYearHoliday  = getPublicHolidayByYear(dstart.get(Calendar.YEAR));
			SecondYearHoliday = getPublicHolidayByYear(dend.get(Calendar.YEAR));
			Calendar d1;
			for(d1 = (Calendar) dstart.clone() ;!d1.after(dend) ; d1.add(Calendar.DATE, 1));
			{
			
				Iterator<Calendar> it = FirstYearHoliday.iterator();
				while(it.hasNext())
				{
					Calendar temp = it.next();
					if(d1.get(Calendar.DATE) == temp.get(Calendar.DATE) && 
							d1.get(Calendar.MONTH) == temp.get(Calendar.MONTH) &&
							d1.get(Calendar.YEAR) == temp.get(Calendar.YEAR))
					{
						 DurationDays--;						
					}
				}	
				Iterator<Calendar> it2 = SecondYearHoliday.iterator();
				while(it2.hasNext())
				{
					Calendar temp = it2.next();
					if(d1.get(Calendar.DATE) == temp.get(Calendar.DATE) && 
							d1.get(Calendar.MONTH) == temp.get(Calendar.MONTH) &&
							d1.get(Calendar.YEAR) == temp.get(Calendar.YEAR))
					{
						 DurationDays--;						
					}
				}			
			}

		}
		else
		{
			FirstYearHoliday  = getPublicHolidayByYear(dstart.get(Calendar.YEAR));
			Calendar d1;
			for(d1 = (Calendar) dstart.clone() ;!d1.after(dend) ;d1.add(Calendar.DATE, 1) )
			{
				Iterator<Calendar> it = FirstYearHoliday.iterator();
				while(it.hasNext())
				{
					Calendar temp = it.next();
					if(d1.get(Calendar.DATE) == temp.get(Calendar.DATE) && 
							d1.get(Calendar.MONTH) == temp.get(Calendar.MONTH) &&
							d1.get(Calendar.YEAR) == temp.get(Calendar.YEAR))
					{
						 DurationDays--;						
					}
				}					
			}
		}
		Calendar d1;
		for(d1 = (Calendar) dstart.clone() ;!d1.after(dend) ;	d1.add(Calendar.DATE, 1) )
		{
			if(d1.get(Calendar.DAY_OF_MONTH) == 6 || d1.get(Calendar.DAY_OF_MONTH) == 7)
			{
				 DurationDays--;
			}
		}
		return DurationDays;
	}
	
	private static ArrayList<Calendar> getPublicHolidayByYear(int year)
	{
		Statement stmt = null;
		ResultSet result = null;
		
		ArrayList<Calendar> dateList = new ArrayList<Calendar>();
		Connection conn;
		try {
			conn = ConnPool.GetConnection();
			stmt = conn.createStatement();			
			String sql = String.format(
					"Select * from ca_schemata.holidays where Year=%d",year);
			result = stmt.executeQuery(sql);
		} catch (DAOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try 
		{
			while(result.next())
			{
				Calendar tempDate = Calendar.getInstance();
				tempDate.setTime(result.getDate("Date"));
				dateList.add(tempDate );
			}
			
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return dateList;

	}
	
	
}
